import pymysql


# 定义类  创建数据库  工具类
class DBTooks:

    # 创建连接
    @staticmethod
    def __create_connect():
        # 连接数据库
        conn = pymysql.connect(host="211.103.136.244",
                               port=7061,
                               user="student",
                               password="iHRM_student_2022",
                               database="ihrm",
                               charset="utf8")
        # 返回连接
        return conn

    # 查询一条数据
    @staticmethod
    def qurey_one(sql):
        conn = None
        cursor = None
        resp = None
        try:
            # 获取连接 使用conn变量接收
            conn = DBTooks.__create_connect()
            # 创建游标 用 cursor变量接收
            cursor = conn.cursor()
            # 执行语句 返回结果 用变量resp接收
            cursor.execute(sql)
            resp = cursor.fetchone()
        except Exception as error:
            print('连接错误', error)
        finally:
            # 关闭 游标
            cursor.close()
            # 关闭 连接
            conn.close()
            # sql语句执行成功显示结果  执行失败为初始值None
            return resp

    # 查询N条数据
    @staticmethod
    def qurey_many(sql, row_number, fetchmany):
        conn = None
        cursor = None
        resp = None
        try:
            conn = DBTooks.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            cursor.rownumber = row_number
            resp = cursor.fetchmany(fetchmany)
        except Exception as error:
            print('连接错误', error)
        finally:
            cursor.close()
            conn.close()
            return resp

    # 查询全部数据
    @staticmethod
    def qurey_all(sql, N):
        conn = None
        cursor = None
        resp = None
        try:
            conn = DBTooks.__create_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            cursor.rownumber = N
            resp = cursor.fetchall()
        except Exception as error:
            print('连接错误', error)
        finally:
            cursor.close()
            conn.close()
            return resp

    # 执行 增删改 操作
    @staticmethod
    def iud(sql):
        conn = None
        cursor = None
        try:
            conn = DBTooks.__create_connect()
            cursor = conn.cursor()
            ret = cursor.execute(sql)
            conn.commit()
            print('删除成功 Affected rows', ret)
        except Exception as error:
            conn.rollback()
            print('回滚', error)
        finally:
            cursor.close()
            conn.close()


if __name__ == '__main__':
    res = DBTooks.qurey_all("select * from t_book",20)
    print(res)
    DBTooks.iud("delete from t_book where id = 20")
    res2 = DBTooks.qurey_one("select * from t_book where id = 28")
    print(res2)